USE [NANS21V]
GO

/****** Object:  StoredProcedure [dbo].[TimeraceTeamMember]    Script Date: 05/12/2014 08:39:02 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TimeraceTeamMember]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[TimeraceTeamMember]
GO

CREATE PROCEDURE [dbo].[TimeraceTeamMember]
	@TAIKAINO int,
	@KYOGINO int,
	@SUBNO int,
	@ROUNDNO int,
	@KUMINO int,
	@LANE int
AS
SELECT
	SOUJUN,
	C.KYOGISYANO,
	NUMBER1,
	NUMBER2,
	KYOGISYAMEI,
	KYOGISYAKANA,
	dbo.Seibetu(SEIBETU) AS SEIBETU,
	GAKUNEN,
	SEINEN,
	SYOZOKUMEI,
	KOJINSYOZOKUTIMEI
FROM
	(SELECT
		A.TAIKAINO,
		A.KYOGINO,
		A.SUBNO,
		A.ROUNDNO,
		A.KUMINO,
		A.LANE,
		SOUJUN,
		A.KYOGISYANO,
		SYOZOKUNO1,
		NUMBER1,
		NUMBER2,
		KYOGISYAMEI,
		KYOGISYAKANA,
		SEIBETU,
		GAKUNEN,
		SEINEN,
		KOJINSYOZOKUTIMEI
	FROM
		TBL_ORDER AS A
		LEFT OUTER JOIN TBL_KYOGISYA AS B
		ON A.TAIKAINO = B.TAIKAINO
		AND A.KYOGISYANO = B.KYOGISYANO) AS C
	LEFT OUTER JOIN TBL_SYOZOKU AS D
	ON C.TAIKAINO = D.TAIKAINO
	AND C.SYOZOKUNO1 = D.SYOZOKUNO
WHERE
	C.TAIKAINO = @TAIKAINO
	AND KYOGINO = @KYOGINO
	AND SUBNO = @SUBNO
	AND ROUNDNO = @ROUNDNO
	AND KUMINO = @KUMINO
	AND LANE = @LANE
ORDER BY
	SOUJUN